﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using Acsys.Entity;

namespace Acsys
{
    public class CrTrRptRepository : Repository
    {
        public CrTrRptRepository()
            : base()
        {
        }

        public DataSet GetListReport(DateTime fromDate, DateTime toDate, bool alldrType, int drTypeID,
            bool allCcy, int ccyID, bool allVessel, int vesselID, bool nonVessel, string status, bool allStatus)
        {
            try
            {
                DataSet ds = new DataSet("ds");
                CreditTransaction e = new CreditTransaction();
                OpenConnection();
                MySqlCommand tempCommand = new MySqlCommand();
                tempCommand.Connection = m_connection;
                tempCommand.CommandText = "delete from table_temp";
                tempCommand.ExecuteNonQuery();

                MySqlCommand aAdapter = new MySqlCommand(e.GetAllSQL(fromDate, toDate, alldrType, drTypeID,
                    allCcy, ccyID, allVessel, vesselID, nonVessel, status, allStatus),
                    m_connection);
                MySqlDataReader aReader = aAdapter.ExecuteReader();
                //string sqlTempc = "insert into table_temp (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
               // string sqlTemp = "";

                string insertSql = "insert into table_temp (c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13) values {0}";
                string valuesTemplate = "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')";
                string values = "";
                
                while (aReader.Read())
                {
                    string ccy = aReader["ccy_code"].ToString();
                    values += "," + string.Format(valuesTemplate, 
                        ((DateTime)aReader["crtr_date"]).ToString("yyyy/MM/dd"),
                        aReader["crtr_code"],
                        aReader["crtr_description"],
                        aReader["crty_code"],
                        aReader["ccy_code"].ToString()=="RP"? aReader["crtr_amount"]:0,
                        aReader["ccy_code"].ToString() == "SGD" ? aReader["crtr_amount"].ToString() :"0",
                        aReader["ccy_code"].ToString() == "USD" ? aReader["crtr_amount"].ToString() :"0",
                        ((DateTime)aReader["crtr_estdate"]).ToString("yyyy/MM/dd"),
                        aReader["crtr_paymentstatus"].ToString(),
                        aReader["ccy_code"].ToString() == "RP" ? (Convert.ToDouble(aReader["crtr_amount"]) - Convert.ToDouble(aReader["crtr_totalpaidamount"]))  : 0,
                        aReader["ccy_code"].ToString() == "SGD" ? (Convert.ToDouble(aReader["crtr_amount"]) - Convert.ToDouble(aReader["crtr_totalpaidamount"])) : 0,
                        aReader["ccy_code"].ToString() == "USD" ? (Convert.ToDouble(aReader["crtr_amount"]) - Convert.ToDouble(aReader["crtr_totalpaidamount"])) : 0,
                        aReader["ves_code"].ToString()
                        );
                }
                aReader.Close();
                if (values != "")
                {
                    values = values.Remove(0, 1);
                    tempCommand.CommandText = string.Format(insertSql, values);
                    tempCommand.ExecuteNonQuery();
                }

                MySqlDataAdapter tempAdapter = new MySqlDataAdapter("select * from table_temp", m_connection);
                tempAdapter.Fill(ds.Tables.Add("Report"));
                return ds;
            }
            catch (Exception x)
            {
                throw new Exception(getErrorMessage(x));
            }
            finally
            {
                m_connection.Close();
            }
        }
        public DataSet GetPrintDataSet(int ID)
        {
            try
            {
                CreditTransaction e = new CreditTransaction();
                OpenConnection();
                MySqlDataAdapter aAdapter = new MySqlDataAdapter(e.GetAllSQL(ID),
                    m_connection);
                DataSet ds = new DataSet("ds");
                aAdapter.Fill(ds.Tables.Add("Report"));
                return ds;
            }
            catch (Exception x)
            {
                throw new Exception(getErrorMessage(x));
            }
            finally
            {
                m_connection.Close();
            }
        }
        public DataSet GetListPaymentReport(DateTime fromDate, DateTime toDate, bool allcrTr
            , int crTrID, bool allCcy, int ccyID, string doctype, bool doctypeall, 
            bool allVessel, int vesselID)
        {
            try
            {
                DataSet ds = new DataSet("ds");
                PaymentCreditTransaction e = new PaymentCreditTransaction();
                OpenConnection();

                MySqlCommand tempCommand = new MySqlCommand();
                tempCommand.Connection = m_connection;
                tempCommand.CommandText = "delete from table_temp";
                tempCommand.ExecuteNonQuery();

                MySqlCommand aAdapter = new MySqlCommand(e.GetAllSQL(fromDate, toDate, allcrTr, crTrID, allCcy,
                    ccyID, doctype, doctypeall, allVessel, vesselID),
                    m_connection);
                MySqlDataReader aReader = aAdapter.ExecuteReader();
                //string sqlTempc = "insert into table_temp (c1,c2,c5,c4,c9,c13,c3) values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}')";
                //string sqlTemp = "";

                string insertSql = "insert into table_temp (c1,c2,c5,c4,c9,c13,c3, c8) values {0}";
                string valuesTemplate = "('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{0}')";
                string values = "";
                
                while (aReader.Read())
                {
                    string ccy = aReader["ccy_code"].ToString();
                    values += ","+string.Format(valuesTemplate, (
                        (DateTime)aReader["pyc_date"]).ToString("yyyy/MM/dd"),
                         aReader["ccy_code"].ToString(),
                         aReader["pyc_amount"],
                         aReader["pyc_doctype"],
                         aReader["crtr_code"],
                         aReader["pyc_bank"],
                         aReader["ves_code"]
                         );
                }
                aReader.Close();
                if (values != "")
                {
                    values = values.Remove(0, 1);
                    tempCommand.CommandText = string.Format(insertSql, values);
                    tempCommand.ExecuteNonQuery();
                }

                MySqlDataAdapter tempAdapter = new MySqlDataAdapter("select * from table_temp", m_connection);
                tempAdapter.Fill(ds.Tables.Add("Report"));
                return ds;
            }
            catch (Exception x)
            {
                throw new Exception(getErrorMessage(x));
            }
            finally
            {
                m_connection.Close();
            }
        }
    }
}
